package com.hongdee.base.imp.service.impl;

import com.alibaba.fastjson.JSON;
import com.hongdee.base.entity.BaseEntity;
import com.hongdee.base.imp.dao.IExcelSchemeDao;
import com.hongdee.base.imp.entity.TExcelScheme;
import com.hongdee.base.imp.entity.TExcelSchemeExp;
import com.hongdee.base.imp.entity.TExpDataVoEntity;
import com.hongdee.base.imp.service.IExcelSchemeService;
import com.hongdee.base.imp.vo.ExcelSchemePropertyVo;
import com.hongdee.base.imp.vo.ExcelSchemeVo;
import com.hongdee.base.util.EntityFactory;
import com.hongdee.common.dao.IImpHibernateDao;
import com.hongdee.common.dao.IImpJdbcDao;
import com.hongdee.common.util.DateUtils;
import com.hongdee.common.util.StringUtil;
import com.hongdee.common.util.TokenUtil;
import com.hongdee.poi.ExcelRowContentCallback;
import com.hongdee.poi.ExcelWorkSheetRowCallbackHandler;
import com.hongdee.poi.PoiExcelUtil;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.*;

/**
 * 名  称:ExcelSchemeServiceImpl
 * 类  型: class
 * 描  述: 用一句话描述类的作用
 * 路  径:com.hongdee.base.imp.service.impl
 * 作  者:My
 * 日  期:2017/2/15 13:50
 */
@Service
public class ExcelSchemeServiceImpl implements IExcelSchemeService {
    private Logger logger = LoggerFactory.getLogger(ExcelSchemeServiceImpl.class);
    private static final String TABLE_NAME = "T_EXCEL_SCHEME";
    @Autowired
    private IExcelSchemeDao excelSchemeDao;

    /**
     * 多数据源
     */
    @Autowired
    private IImpHibernateDao impHibernateDao;
    @Autowired
    private IImpJdbcDao impJdbcDao;


    @Override
    public boolean changeStatus(String id, String status) {
        return false;
    }

    @Override
    public void saveOrUpdate(TExcelScheme tes) throws Exception {
        Map<String, Object> entity = this.excelSchemeDao.info("t_data_entity", "id", tes.getDataEntityId());
        tes.setDataEntityTitle(String.valueOf(entity.get("entityTitle")));
        tes.setQualifiedName(String.valueOf(entity.get("qualifiedName")));
        //tes.setBrand(String.valueOf(entity.get("brand")));
        excelSchemeDao.saveOrUpdate(tes);
    }

    @Override
    public ExcelSchemeVo loadExcelSchemeInfo(String schemeId) throws Exception {
        if (StringUtils.isEmpty(schemeId)) {
            throw new Exception("请选择方案");
        }

        Map schemeMap = excelSchemeDao.info(TABLE_NAME, "id", schemeId);
        if (null == schemeMap || schemeMap.size() == 0) {
            throw new Exception("请选择有效的方案！");
        }

        ExcelSchemeVo esv = new ExcelSchemeVo();
        esv.setId((String) schemeMap.get("id"));
        esv.setDataEntityId((String) schemeMap.get("dataEntityId"));
        esv.setClsQualifiedName((String) schemeMap.get("qualifiedName"));
        esv.setBrand((String) schemeMap.get("brand"));
        if (StringUtils.isEmpty(esv.getClsQualifiedName())) {
            throw new Exception("请选择有效的方案！");
        }
        esv.setTitleRow(Integer.valueOf(schemeMap.get("titleRow").toString()));
        esv.setDataStartRow(Integer.valueOf(schemeMap.get("dataStartRow").toString()));
        esv.setKeyColumn(Integer.valueOf(schemeMap.get("keyColumn").toString()));

        ///设置导入标识
        esv.setUploadKey(UUID.randomUUID().toString());


        String sqlStr = "SELECT s.id, s.property_id, s.column_num, d.property_name, d.property_type,s.is_merge,"
                +" s.field_len,s.field_type,s.field_empty,s.property_title "
                +" from t_excel_scheme_property  s LEFT JOIN t_data_entity_property  d on s.property_id=d.id "
                +" where s.column_num > 0 and s.scheme_id= ? ";

        List<Map<String, Object>> propMap = excelSchemeDao.list(sqlStr, new Object[]{schemeId});
        if (null != propMap && propMap.size() > 0) {
            List<ExcelSchemePropertyVo> espList = new ArrayList<ExcelSchemePropertyVo>();
            ExcelSchemePropertyVo esp = null;
            for (int i = 0; i < propMap.size(); i++) {
                esp = new ExcelSchemePropertyVo();
                esp.setPropertyTitle(StringUtil.formatEmpty(propMap.get(i).get("propertyTitle")));
                esp.setPropertyName(StringUtil.formatEmpty(propMap.get(i).get("propertyName")));
                esp.setPropertyType(StringUtil.formatEmpty(propMap.get(i).get("propertyType")));
                esp.setColumnNum(Integer.valueOf(propMap.get(i).get("columnNum").toString()));
                //是否合并单元格
                esp.setIsMerge(String.valueOf(propMap.get(i).get("isMerge")));
                esp.setFieldEmpty(String.valueOf(propMap.get(i).get("fieldEmpty")));
                esp.setFieldLen(String.valueOf(propMap.get(i).get("fieldLen")));
                esp.setFieldType(String.valueOf(propMap.get(i).get("fieldType")));
                espList.add(esp);
            }

            esv.setProperyList(espList);
        }

        return esv;

    }

    /**
     * 分页查询方案记录
     *
     * @param start
     * @param limit
     * @param templateName
     * @return
     */
    @Override
    public Map page(String start, String limit, String templateName, String brand) throws Exception {

        String sqlStr = "select * from t_excel_scheme where 1 =1  ";
        List params = new ArrayList();
        if (!StringUtils.isEmpty(templateName)) {
            sqlStr += " and scheme_title like ? ";
            params.add("%" + templateName + "%");
        }
        if (!StringUtils.isEmpty(brand)) {
            sqlStr += " and brand = ? ";
            params.add(brand);
        }
        return this.excelSchemeDao.page(sqlStr, params.toArray(), start, limit);
    }

    /**
     * 上传文件
     * @param file
     * @param path
     * @param schemeId
     * @throws Exception
     */
    @Override
    public void uploadTemp(MultipartFile file,String path,String schemeId) throws Exception {
        //获得导入方案信息
        TExcelScheme scheme=this.excelSchemeDao.getExcelSchemeById(schemeId);
        //获得上传的模板信息
        TExcelSchemeExp exp=this.excelSchemeDao.getExcelSchemeExpByBrandAndEntity(scheme.getDataEntityId(),scheme.getBrand());
        if(exp==null){
            exp=new TExcelSchemeExp();
        }
        String fileName = file.getOriginalFilename();
        String type=fileName.substring(fileName.lastIndexOf(".")+1,fileName.length());
        String excelName=fileName.substring(0,fileName.lastIndexOf("."));
        if(!type.equals("xls") && !type.equals("xlsx")){
            throw new Exception("请选择正确的Excel模板文件!!");
        }
        fileName=scheme.getDataEntityId()+"."+type;
        File targetFile = new File(path, fileName);
        File rootFile=targetFile.getParentFile();
        if(!rootFile.exists()){
            rootFile.mkdirs();
        }
        file.transferTo(targetFile);
        exp.setExcelPath(path+File.separator+fileName);
        exp.setExcelName(excelName);
        exp.setBrand(scheme.getBrand());
        exp.setDataEntityId(scheme.getDataEntityId());
        this.excelSchemeDao.saveOrUpdate(exp);
    }

    /**
     * 下载模板获得详情信息
     * @param schemeId
     * @return
     * @throws Exception
     */
    @Override
    public Map downLoadInfoBySchemeId(String schemeId) throws Exception {
        String sql=" select exp.brand,exp.data_entity_id,exp.excel_name,exp.excel_path from t_excel_scheme_exp exp,t_excel_scheme scheme " +
                " where exp.data_entity_id=scheme.data_entity_id and exp.brand=scheme.brand and scheme.id=? limit 1";
        return this.excelSchemeDao.info(sql,schemeId);
    }

    /**
     * 更新记录状态
     *
     * @param id
     * @param s
     * @throws Exception
     */
    @Override
    public void update(String id, String s) throws Exception {
        String hql = " update TExcelScheme set status =:status where id=:id";
        Map params = new HashMap();
        params.put("id", id);
        params.put("status", s);
        this.excelSchemeDao.update(hql, params);
    }

    @Override
    public void saveImpObj(BaseEntity be) throws Exception {
        impHibernateDao.saveEntity(be);

    }

    /***
     * 解析 Excel
     * @param file
     * @param scheme
     * @return
     */
    @Override
    public Map parseExcel(MultipartFile file, ExcelSchemeVo scheme, String uid,String path) throws Exception {
        logger.info("开始导入：" + DateUtils.getTimeStr());
        String fileName = file.getOriginalFilename();
        String type = fileName.substring(fileName.lastIndexOf("."));
        String companyId = TokenUtil.getCompanyId(uid);
        Map map=new HashMap();
        if (type != null && (type.endsWith("xlsx") || type.endsWith("xls"))) {
            //2007之后版本
            int successNum[] = {0, 0};
            TExpDataVoEntity expData=new TExpDataVoEntity();
            try {
                ExcelWorkSheetRowCallbackHandler sheetRowCallbackHandler =
                        new ExcelWorkSheetRowCallbackHandler(new ExcelRowContentCallback() {
                            public void processRow(int rowNum, Map<String, String> map) throws Exception {
                                logger.error("数据" + JSON.toJSONString(map));
                                if (scheme.getDataStartRow() - 1 <= rowNum && null != map) {
                                    BaseEntity be = EntityFactory.BuildEntity(scheme, new ArrayList(map.values()), uid, companyId,expData);
                                    if (null != be) {
                                        successNum[0]++;
                                        impHibernateDao.saveEntity(be);
                                    }
                                }
                            }
                        });

                sheetRowCallbackHandler.setHeadRow(scheme.getTitleRow() - 1);
                PoiExcelUtil.readExcel(type, file.getInputStream(), 0, sheetRowCallbackHandler, null);
                logger.info("结束导入：" + DateUtils.getTimeStr());
                String fileId=createExcel(expData.getHeadList(),expData.getBodyList(),path,scheme.getClsQualifiedName());
                map.put("msg","导入成功" + successNum[0] + "行");
                map.put("success",true);
                map.put("fileId",fileId);
                map.put("total",0L + successNum[0]);
            } catch (RuntimeException are) {
                are.printStackTrace();
            } catch (Exception e) {
                e.printStackTrace();
            }

            return map;
        } else {
            throw new Exception("无法识别的文件格式！");
        }


    }


    public String createExcel(List<String> headList,List<List<Object>> bodyList,String path,String sheetName) throws IOException {

        if (bodyList != null && bodyList.size() > 0) {
            String fileId = StringUtil.UUID();
            XSSFWorkbook wb = new XSSFWorkbook();
            XSSFSheet sheet = wb.createSheet("任务详情");
            XSSFRow row = sheet.createRow((int) 0);
            XSSFCellStyle style = wb.createCellStyle();
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            for (int i = 0; i < headList.size(); i++) {
                XSSFCell cell = row.createCell(i);
                cell.setCellValue(headList.get(i));
                cell.setCellStyle(style);
                sheet.autoSizeColumn(i);
                sheet.setDefaultRowHeightInPoints(24);
                sheet.setColumnWidth(cell.getColumnIndex(), 256 * 30);
            }
            /*****
             * int的最大值是2147483647，不会越界
             * 理论上可导出的数据量不会超过65535,出现这种情况之后，再进行优化处理
             * **/

            int dataSize = bodyList.size();
            String temp;
            for (int i = 0; i < dataSize; i++) {
                List<Object> list = bodyList.get(i);
                row = sheet.createRow(i + 1);
                for (int j = 0; j < list.size(); j++) {
                    row.createCell(j).setCellValue(String.valueOf(list.get(j)));
                }
            }
            File filepack = new File(path);
            if (!filepack.exists()) {
                filepack.mkdir();
            }
            String filePath = path + File.separator + fileId + ".xlsx";
            File file = new File(filePath);
            file.createNewFile();
            FileOutputStream fileOut = new FileOutputStream(file);
            wb.write(fileOut);
            fileOut.close();
            return fileId;
        }
        return "";

    }

    /**
     * 下拉选择导入方案模板
     *
     * @return
     */
    @Override
    public List combo(String uid) throws Exception {

        if (StringUtils.isEmpty(uid)) {
            throw new Exception("请先登录！");
        }

        Map companyInfo = TokenUtil.getCompany(uid);
        String brand = String.valueOf(companyInfo.get("oCarBrand"));

        List params = new ArrayList();
        String sql = " select * from " + TABLE_NAME + " where status ='1'";
        if ("null".equals(brand)) {
            return new ArrayList();
        }

        sql += " and brand = ? ";
        params.add(brand);


        List<Map<String, Object>> mapList = this.excelSchemeDao.list(sql, params.toArray());
        List<Map> resList = new ArrayList<Map>();
        Map temp;
        for (Map map : mapList) {
            temp = new HashMap();
            temp.put("label", map.get("schemeTitle"));
            temp.put("value", map.get("id"));
            resList.add(temp);
        }
        return resList;
    }


}

